package com.api.gbhg.zengping.dao;

import cn.hutool.core.util.StrUtil;
import com.api.gbhg.liwenjun.VersionFlowUtil;
import com.api.gbhg.luoao.tool.ToolCURD;
import com.api.gbhg.zengping.page.PageInfo;
import com.api.gbhg.zengping.web.BudgetingListAction;
import com.google.common.base.Strings;
import com.kingbase8.util.LOGGER;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.workflow.workflow.WorkflowVersion;

import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.util.*;

/**
 * @program: gbhg-oa
 * @author: Mr.zeng
 * @create: 2021-01-26 13:49
 *
 * 预算编制列表DAO
 **/
public class BudgetingListDao {
    private static final Logger logger = LoggerFactory.getLogger("gbhg");
    private final PropertyManagementCostDao rmcd=new PropertyManagementCostDao();
    public static  BudgetingListDao budgetingDao=new BudgetingListDao();


    private static String table1 ;
    private static String table2 ;
    private static String table18 ;
    private static String lcTypeId ;


    static{
        BaseBean baseBean = new BaseBean();
        //海关离退休人员工资津贴补贴经费新增表
        String workflowId = WorkflowVersion.getActiveVersionWFID(baseBean.getPropValue("FormationWorkflowid", "BZ01Workflowid"));//读取配置文件
        table1 = VersionFlowUtil.getFlowTableName(workflowId);
        //通用项目新增表
        String workflowId2 = WorkflowVersion.getActiveVersionWFID(baseBean.getPropValue("FormationWorkflowid", "BZ02Workflowid"));
        table2 = VersionFlowUtil.getFlowTableName(workflowId2);
        //总署下发项目导入
        String workflowId18 = WorkflowVersion.getActiveVersionWFID(baseBean.getPropValue("FormationWorkflowid", "BZ18Workflowid"));
        table18 = VersionFlowUtil.getFlowTableName(workflowId18);
        //流程类型(预算编制)
        lcTypeId = baseBean.getPropValue("FormationWorkflowid", "workflowType");
    }
    /**
     * Nd == 年度
     * DW == 单位
     * Project  == 项目
     * @param param
     * @return
     */
    public RecordSet getSelectByNdAndDwAndProject(Map<String,Object> param){
        String sql="select requestId,xmmin,fzr,sbdw,sbks,yszje,lczt,lcbj from uf_jingfei_version v where 1=1";
        RecordSet rs=new RecordSet();
        StringBuffer sb=new StringBuffer();
        sb.append(sql);
        // 年度
        Integer ysnd = (Integer) param.get("ysnd");
        // isPresent 写不为空的逻辑
        if(Optional.ofNullable(ysnd).isPresent()){
            sb.append(" and ysnd="+ysnd);
        }
        // 单位
        Integer sbdw = (Integer) param.get("sbdw");
        if(Optional.ofNullable(sbdw).isPresent()){
            sb.append(" and sbdw="+sbdw);
        }
        // 项目流程对应lcbj
        String xm = (String) param.get("xm");
        if(!Strings.isNullOrEmpty((xm))){
            sb.append(" and lcbj="+xm);
        }
        int pageNum= (int) param.get("pageNum");
        int pageSize= (int) param.get("pageSize");
        pageNum=(pageNum-1)*pageSize;
        // 判断是mysql 还是sqlserver
        if("sqlserver".equals(rs.getDBType())){
            sb.append(" order by lcjlsqjlid desc offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        logger.info("预算编制列表sql--:"+sb.toString());
        rs.executeQuery(sb.toString());
        return rs;
    }

    /**
     * 4-21 up by lwb
     * @param param
     * @return
     */
    public PageInfo getSelectByNdAndDwAndProject2(Map<String,Object> param){
//        String sql="select requestId,xmmin,fzr,sbdw,sbks,yszje,lczt,lcbj from uf_jingfei_version v where 1=1";
        logger.info(" 进入筛选编制流程数据方法---");
        int pageNum= (int) param.get("pageNum");
        int pageSize= (int) param.get("pageSize");
        pageNum=(pageNum-1)*pageSize;
        String condition = "";
        if (param.get("xm") !=null && param.get("ysnd") !=null){
                condition = " and ba.workflowname like '%"+param.get("xm")+"%' and rq.createdate like '%"+param.get("ysnd")+"%' ";
        }else if(param.get("xm") !=null &&param.get("ysnd") ==null){
            condition = " and ba.workflowname like '%"+param.get("xm")+"%'  ";
        }else if(param.get("xm")==null&&param.get("ysnd")!=null){
            condition = " and rq.createdate like '%"+param.get("ysnd")+"%' ";
        }

        RecordSet rs=new RecordSet();
        StringBuffer sql = new StringBuffer();
        if (!param.get("sydw").equals("")) {
            sql = new StringBuffer(" select bi.tablename,rq.requestid,rq.status,rq.workflowid from workflow_requestbase rq join workflow_base ba on rq.workflowid = ba.id " +
                    "join workflow_type ty on ba.workflowtype = ty.id " +
                    "join workflow_bill bi on ba.formid = bi.id " +
                    " where rq.requestid in ("+param.get("requestId")+") and ty.id in ("+lcTypeId+") "+condition+" ");
            logger.info("编制列表sql>"+param.get("requestId"));
            logger.info("编制列表sql"+sql);
            String ScreenId = getReqIdScreenDw(sql.toString(),param.get("sydw").toString());
            sql = new StringBuffer(" select bi.tablename,rq.requestid,rq.status,rq.workflowid from workflow_requestbase rq join workflow_base ba on rq.workflowid = ba.id " +
                    "join workflow_type ty on ba.workflowtype = ty.id " +
                    "join workflow_bill bi on ba.formid = bi.id " +
                    " where rq.requestid in ("+ScreenId+") and ty.id in ("+lcTypeId+") "+condition+" " +
                    " order by rq.requestid desc offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else{
            sql = new StringBuffer(" select bi.tablename,rq.requestid,rq.status,rq.workflowid from workflow_requestbase rq join workflow_base ba on rq.workflowid = ba.id " +
                    "join workflow_type ty on ba.workflowtype = ty.id " +
                    "join workflow_bill bi on ba.formid = bi.id " +
                    " where rq.requestid in ("+param.get("requestId")+") and ty.id in ("+lcTypeId+") "+condition+" " +
                    " order by rq.requestid desc offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }

        rs.executeQuery(sql.toString()); //筛选编制流程sql
        logger.info("筛选编制流程sql"+sql);
        HashMap<String,Object>[] data=new HashMap[rs.getArray().size()];
        Set<String> set = BudgetingListAction.getIsGDSBProject();
        int i=0;
        while (rs.next()){
            RecordSet rss = new RecordSet();
            String tablename = rs.getString("tablename");
            String tablenamemx;
            String hj = "";
            if (tablename.equals(table2)){//特殊处理
                tablenamemx = tablename+"_dt4";
            }else{
                tablenamemx = tablename+"_dt2";
            }
            if (tablename.equals(table1))
                hj = "hj";
            else
                hj = "hjwy";
            StringBuffer sql2 = new StringBuffer("");
            if (tablename.equals(table18)){
                sql2.append(" select m.requestId,m.sheet1_main_field5 xmmc,m.cjr fzr,m.cjrdw sbdw,m.cjrks sbks,CONVERT(varchar(200),m.sydw) sydw " +
                        " from "+tablename+" m  " +
                        " where m.requestid = "+rs.getString("requestid")+" ");
                if (!param.get("sydw").equals("")){
                    sql2.append(" and CHARINDEX(','+ '"+param.get("sydw")+"' +',',','+ CONVERT(varchar(200),sbdw) +',')>0  group by m.requestId,m.sheet1_main_field5,m.cjr,m.cjrdw,m.cjrks,CONVERT(varchar(200),m.sydw) ");
                }else{
                    sql2.append(" group by m.requestId,m.sheet1_main_field5,m.cjr,m.cjrdw,m.cjrks,CONVERT(varchar(200),m.sydw) ");
                }


            }else{
//                sql2 = " select m.requestId,m.xmmc,m.fzr,m.sbdw,m.sbks,sum(d."+hj+") yszje from "+tablename+" m " +
//                        " join "+tablenamemx+" d on m.id = d.mainid where m.requestid = "+rs.getString("requestid")+" group by m.requestid,m.xmmc,m.fzr,m.sbdw,m.sbks ";
                sql2.append(" select m.requestId,m.xmmc,m.fzr,m.sbdw,m.sbks,m.id,CONVERT(varchar(200),m.sydw) sydw  from "+tablename+" m " +
                        "  where m.requestid = "+rs.getString("requestid")+" ");

                if (!param.get("sydw").equals("")){
                    sql2.append(" and CHARINDEX(','+ '"+param.get("sydw")+"' +',',','+ CONVERT(varchar(200),sbdw) +',')>0 group by m.requestid,m.xmmc,m.fzr,m.sbdw,m.sbks,m.id,CONVERT(varchar(200),m.sydw)");
                }else{
                    sql2.append(" group by m.requestid,m.xmmc,m.fzr,m.sbdw,m.sbks,m.id,CONVERT(varchar(200),m.sydw)");
                }
            }

            rss.executeQuery(sql2.toString());
            while (rss.next()){
                data[i]=new HashMap<>();
                //序号
                data[i].put("requestId",rss.getString("requestId"));
                // 是否固定申报项目
                boolean flag = set.contains(rss.getString("lcbj"));
                data[i].put("isGDSBXM",flag==true?"是":"否");
                //项目名称
                data[i].put("xmmc",rss.getString("xmmc"));
                //编报人
                RecordSet jbr = rmcd.getUserNameById(rss.getInt("fzr"));
                jbr.next();
                String lastname = jbr.getString("lastname");
                if(lastname==""){
                    lastname="系统管理员";
                }
                data[i].put("fzr",lastname);
                //使用单位
                if (!rss.getString("sydw").equals("")){
                    String sydw = getDwNameyIds(rss.getString("sydw"));
                    data[i].put("sydw",sydw);
                }else{
                    data[i].put("sydw","");
                }

                // 申报单位
                if (!rss.getString("sbdw").equals("")){
                    String sbdw = getDwNameyIds(rss.getString("sbdw"));
                    data[i].put("sbdw",sbdw);
                }else{
                    data[i].put("sbdw","");
                }


                // 申报科室/部门
                if (!rss.getString("sbks").equals("")){
                    RecordSet sbks = rmcd.getSBKSById(rss.getInt("sbks"));
                    sbks.next();
                    data[i].put("sbks",sbks.getString("departmentmark"));
                }else{
                    data[i].put("sbks","");
                }


                //项目级别名称
                data[i].put("xmjbmc","");

                if(tablename.equals(table18)){
                    String yszje1 = getTable18yszje(rs.getString("requestid"),tablename+"_dt2");

                    if (StringUtils.isBlank(yszje1)){
                        yszje1="0.00";
                    }
                    BigDecimal yszje = new BigDecimal(yszje1);
                    data[i].put("yszje",yszje.divide(new BigDecimal("10000"),4, BigDecimal.ROUND_HALF_UP));
                }else{
                    // 费用合计
//                String yszje1 = rss.getString("yszje");
                    String yszje1 = getmxhj(rss.getString("id"), tablenamemx, hj);

                    if (StringUtils.isBlank(yszje1)){
                        yszje1="0.00";
                    }
                    BigDecimal yszje = new BigDecimal(yszje1);
                    data[i].put("yszje",yszje.divide(new BigDecimal("10000"),4, BigDecimal.ROUND_HALF_UP));
                }


                //项目来源
                data[i].put("xmly","");
                //编制场景
                data[i].put("bzcj","");
                //审批状态
                String requestId = rss.getString("requestId");
                data[i].put("lczt", ToolCURD.Processnode(requestId));
                System.err.println("显示预算信息");
                System.err.println(data[i]);
                i++;
            }
        }

        PageInfo pageInfo=new PageInfo();
        pageInfo.setPageNum((Integer) param.get("pageNum"));
        pageInfo.setPageSize((Integer) param.get("pageSize"));
        //pageInfo.setTotal(gettotal(param.get("requestId").toString(),condition,lcTypeId,param.get("sydw").toString()));
        pageInfo.setTotal(data.length);
        pageInfo.setList(data);
        return pageInfo;
    }

    public static String getReqIdScreenDw(String sql,String sydw){
        RecordSet rs= new RecordSet();
        rs.executeQuery(sql);
        StringBuffer list = new StringBuffer();
        int i=0;
        while (rs.next()){
            RecordSet rs2 = new RecordSet();
            rs2.execute(" select * from " + rs.getString("tablename") + " where requestid = " + rs.getString("requestid") + " and CHARINDEX(','+ '"+sydw+"' +',',','+ CONVERT(varchar(200),sbdw) +',')>0 ");
            if (rs2.next()){
                i++;
                list.append(rs.getString("requestid")+",");
            }
        }
        logger.info("getReqIdScreenDw---"+list.toString());
        if (!list.toString().equals("")){
            return list.toString().substring(0,list.toString().length()-1);
        }else{
            return "null";
        }
    }

    /**
     * 根据一个或多个id查询出单位的显示值
     * @param ids
     * @return
     */
    public static String getDwNameyIds(String ids){
        RecordSet rts=new RecordSet();
        String sql="select subcompanyname from HrmSubCompany where id in ("+ids+")";
        StringBuffer dwstr = new StringBuffer("");
        try {
            logger.info("申报科室查询sql---------->"+sql);
            rts.executeQuery(sql);
            while (rts.next()){
                dwstr.append(rts.getString("subcompanyname")+",");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (!dwstr.toString().equals("")){
            return dwstr.toString().substring(0,dwstr.toString().length()-1);
        }else{
            return "";
        }

    }

    public static String getTable18yszje(String requestId,String tabName){
        RecordSet rs = new RecordSet();
        String sql = " select sum(sheet4_detail2_field3) yszje from "+tabName+" where mainid = "+requestId+" ";
        rs.executeQuery(sql);
        String yszje = "";
        while (rs.next()){
            yszje = rs.getString("yszje");
        }
        return yszje;
    }

    /**
     * 获取明细数据
     * @param id
     * @param tabName
     * @return
     */
    public static String getmxhj(String id,String tabName,String jeStr){
        RecordSet rs = new RecordSet();
        String sql = " select sum(d."+jeStr+") yszje from "+tabName+" d where d.mainid = "+id+" ";
        rs.executeQuery(sql);
        String yszje = "";
        while (rs.next()){
            yszje = rs.getString("yszje");
        }
        return yszje;
    }



    public Integer gettotal(String requestId,String condition,String lclx,String sydw){
        String sql = " select bi.tablename,rq.requestid,rq.status,rq.workflowid from workflow_requestbase rq join workflow_base ba on rq.workflowid = ba.id " +
                "join workflow_type ty on ba.workflowtype = ty.id " +
                "join workflow_bill bi on ba.formid = bi.id " +
                " where rq.requestid in ("+requestId+") and ty.id in ("+lclx+") "+condition+" ";
        logger.info("预算编制列表分页总条数sql---:"+sql);
        RecordSet rs=new RecordSet();
        rs.executeQuery(sql);
        if (!sydw.equals("")) {
            int i = 0;
            RecordSet rs2 = new RecordSet();
            while (rs.next()) {
                rs2.executeQuery(" select * from " + rs.getString("tablename") + " where requestid = "+rs.getString("requestid")+" and CHARINDEX(','+ '"+sydw+"' +',',','+ CONVERT(varchar(200),sbdw) +',')>0  ");
                i+=rs2.getCounts();
            }
            return i;
        }else{
            return rs.getCounts();
        }
    }




    public RecordSet getSelectByNdAndDwAndProjectCount(Map<String,Object> param){
        String sql="select count(1) num from uf_jingfei_version v where 1=1";
        RecordSet rs=new RecordSet();
        StringBuffer sb=new StringBuffer();
        sb.append(sql);
        String xm = (String) param.get("xm");
        if(!Strings.isNullOrEmpty((xm))){
            sb.append(" and lcbj="+xm);
        }
        // 年度
        Integer ysnd = (Integer) param.get("ysnd");
        // isPresent 写不为空的逻辑
        if(Optional.ofNullable(ysnd).isPresent()){
            sb.append(" and ysnd="+ysnd);
        }
        // 单位
        Integer sbdw = (Integer) param.get("sbdw");
        if(Optional.ofNullable(sbdw).isPresent()){
            sb.append(" and sbdw="+sbdw);
        }
        rs.executeQuery(sb.toString());
        return rs;
    }


    public static String getSql(Map<String,Object> param){
        StringBuffer sb=new StringBuffer();
        Integer ysnd = (Integer) param.get("ysnd");
        // isPresent 写不为空的逻辑
        if(Optional.ofNullable(ysnd).isPresent()){
            sb.append(" and ysnd="+ysnd);
        }
        // 单位
        Integer sbdw = (Integer) param.get("sbdw");
        if(Optional.ofNullable(sbdw).isPresent()){
            sb.append(" and sbdw="+sbdw);
        }
        // 项目流程对应lcbj
        String xm = (String) param.get("xm");
        if(!Strings.isNullOrEmpty((xm))){
            sb.append(" and lcbj="+xm);
        }
        return sb.toString();
    }

    public static void main(String[] args) {
        HashMap<String,Object> map=new HashMap<>();
        System.out.println(getSql(map));
    }
}
